package com.it.jpa.repository;

import com.it.jpa.domain.Contract;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface ContractRespository extends JpaRepository<Contract,Integer> {

    @Modifying
    @Query("update Contract c set c.contactName=?1 where c.id=?2")
    Integer updateById(String contactName,Integer id);

    @Modifying
    @Query("delete from Contract c where c.id=?1")
    void deleteById(Integer id);

    @Query("select c from Contract c where c.contractNo=?1 and c.contractType=?2 and c.contactName=?3")
    Contract findByCondition(String contractNo, String contractType, String contactName);

    /**
     * 加入：nativeQuery注解时，写原生sql，支持limit函数
     * 加入：nativeQuery注解时是JPQL。JPQL不支持limit函数
     **/
    @Query(nativeQuery = true,value = "select * from contract where contract.contract_status = ?1 " +
            "and DATE_FORMAT(now(),'%m' ) = DATE_FORMAT(contract.contract_startdate,'%m' )")
    List<Contract> findByContractStatus(Boolean contractStatus);

    /**
     * 加入：nativeQuery注解时，写原生sql，支持limit函数
     * 加入：nativeQuery注解时是JPQL。JPQL不支持limit函数
     **/
    @Query(nativeQuery = true,value = "select * from contract where " +
            "d_contract.contract_no = ?3  limit ?1,?2")
    List<Contract> findByPage(Integer startIndex,Integer PageSize,String contractNo);

}
